﻿'Imports System.Data
'Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Acad_DatasetsFormV2

    Dim GridFields As DataView
    Dim NotesDV As DataView

    Private Sub Acad_DatasetsFormV2_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

        DetailsPanel.Visible = False

        PeriodBox.zConnectionString = StoreConnect

        PeriodBox.zSelectedPeriod_ID = CurrentPeriodID

        'ProviderBox.zProviderTypeCodeFilter = "Acade"

        ProviderBox.zConnectionString = StoreConnect


        LoadDatasetBox()


    End Sub

    Private Sub LoadDatasetBox()
        Dim sql As String

        If PeriodBox.zSelectedPeriod_ID < 2014151 Then

            'sql = "select * from Acad_GAG_Datasets WHERE (Dataset_Code <= 200) OR (Dataset_Code > 300) union select Dataset_Code, 'Schools Census Data Set used for 13/14 MFG' as Dataset_Description  from Acad_GAG_Datasets where Dataset_Code = 201 order by Dataset_Description"
            sql = "select * from Acad_GAG_Datasets order by Dataset_Description"
        Else
            'sql = "select * from Acad_GAG_Datasets WHERE ((Dataset_Code >= 201) AND (Dataset_Code < 300)) OR Dataset_Code IN (190,1200) order by Dataset_Description"
            sql = "select * from Acad_GAG_Datasets WHERE Dataset_Code in (260, 190, 250) order by Dataset_Description"

        End If



        LoadComboBox(DatasetBox, sql, "Dataset_Code", "Dataset_Description")

    End Sub


    Private Sub LoadDetails()

        'bind the repeater to a datasource and bind the fields to fields in my datasource

        If ProviderBox.zSelectedProvider.RID = "" Then
            TheRepeater.DataSource = Nothing
            Exit Sub
        End If
        GridPanel.Visible = False

        DetailsPanel.Dock = DockStyle.Fill
        DetailsPanel.Visible = True

        Dim TheData As DataView

        'Dim sql As String

        'sql = "exec dbo.spAcademy_Datasets @dataset=" & DatasetBox.SelectedValue & ", @userperiod=" & PeriodBox.zSelectedPeriod_ID & ", @rid='''" & ProviderBox.zSelectedProvider.RID & "''', @ShowFieldCode=1"

        'TheData = LoadDataView(sql)

        Dim Params As New List(Of Object)

        Params.Clear()
        Params.Add(ProviderBox.zSelectedProvider.RID)
        Params.Add(PeriodBox.zSelectedPeriod_ID)
        Params.Add(DatasetBox.SelectedValue)

        If DoSomeWork("LoadAcadDataset", Params) <> Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If

        TheData = TryCast(BusyButFine.ReturnedValues(0), DataView)

        If TheData Is Nothing Then
            Exit Sub
        End If

        Dim TheTable As New DataTable

        TheTable.Clear()
        TheTable.Columns.Clear()

        TheTable.Columns.Add("Description", GetType(String))
        TheTable.Columns.Add("CurrentValue", GetType(String))
        TheTable.Columns.Add("RevisedValue", GetType(String))
        TheTable.Columns.Add("FieldCode", GetType(Integer))
        TheTable.Columns.Add("Period", GetType(Integer))
        TheTable.Columns.Add("Type", GetType(String))
        TheTable.Columns.Add("YNValue", GetType(String))
        TheTable.Columns.Add("DateValue", GetType(String))
        TheTable.Columns.Add("Formula", GetType(String))
        TheTable.Columns.Add("Status", GetType(Integer))
        TheTable.Columns.Add("Old_Status", GetType(Integer))

        Dim c As DataColumn


        For Each c In TheData.ToTable.Columns

            Dim FieldCode As Integer
            Dim Period As Integer

            Try
                FieldCode = Mid(c.ColumnName, 1, 3)
                Period = Mid(c.ColumnName, 4, 2).Replace("_", "")


            Catch ex As Exception
                FieldCode = 0
            End Try

            If FieldCode <> 0 Then

                Dim FieldName As String
                Dim CurrentValue As String
                Dim RevisedValue As String
                Dim TheDataType As DataView
                Dim Type As String
                Dim DateValue As String
                Dim YNValue As String
                Dim IsFormula As String


                TheDataType = LoadDataView("select top 1 * from Acad_GAG_Templates where Dataset_Code=" & DatasetBox.SelectedValue & " and Field_Code = " & FieldCode)

                Type = TheDataType(0)("Type")

                FieldName = Mid(c.ColumnName, c.ColumnName.IndexOf("_") + 2, Len(c.ColumnName)).Replace("FORMULA_", "")

                If TheData.Count = 0 Then
                    CurrentValue = ""

                Else

                    CurrentValue = IIf(IsDBNull(TheData(0)(c.ColumnName)), "", TheData(0)(c.ColumnName))
                End If

                RevisedValue = ""
                DateValue = ""
                YNValue = ""

                If CurrentValue <> "" Then

                    Select Case Type
                        Case "DATE"
                            CurrentValue = FormatDateTime(CurrentValue, DateFormat.ShortDate)

                        Case "INT"
                            CurrentValue = FormatNumber(CurrentValue, 0, TriState.True, TriState.False, TriState.True)

                        Case "MONEY"
                            CurrentValue = "£" & FormatNumber(CurrentValue, 2, TriState.True, TriState.False, TriState.True)

                        Case "PERCENT"
                            CurrentValue = FormatNumber(CurrentValue * 100, 2, TriState.True, TriState.False, TriState.True) & "%"

                        Case "YN"
                            CurrentValue = IIf(CurrentValue = False, "No", "Yes")

                        Case Else

                    End Select
                End If

                If c.ColumnName.Contains("FORMULA_") Then
                    IsFormula = "FORMULA"
                Else
                    IsFormula = ""
                End If

                Dim Status As Integer

                Status = GetStatus(DatasetBox.SelectedValue, FieldCode, PeriodBox.zSelectedPeriod_ID, ProviderBox.zSelectedProvider.RID)

                TheTable.Rows.Add(FieldName, CurrentValue, RevisedValue, FieldCode, Period, Type, YNValue, DateValue, IsFormula, Status, Status)

            End If
        Next

        'bind it!!

        DescriptionLabel.DataBindings.Clear()
        DescriptionLabel.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheTable, "Description"))

        CurrentValueLabel.DataBindings.Clear()
        CurrentValueLabel.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheTable, "CurrentValue"))

        RevisedValueBox.DataBindings.Clear()
        RevisedValueBox.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheTable, "RevisedValue"))

        YesNoBox.DataBindings.Clear()
        YesNoBox.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheTable, "YNValue"))

        DateBox.DataBindings.Clear()
        DateBox.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheTable, "DateValue"))

        comboBoxStatus.DataBindings.Clear()
        comboBoxStatus.DataBindings.Add(New System.Windows.Forms.Binding("SelectedIndex", TheTable, "Status"))


        TheRepeater.DataSource = TheTable


    End Sub


    Private Sub ProviderBox_ProviderSelected(ByVal e As TheStore.ProviderSearchBoxCtrl.SelectedProviderClass) Handles ProviderBox.ProviderSelected

        LoadDetails()

    End Sub

    Private Sub DatasetBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DatasetBox.SelectedIndexChanged
        GridPanel.Visible = False

        LoadDetails()
        TheRepeater.Focus()
    End Sub

    Private Sub PeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged
        GridPanel.Visible = False
        LoadDatasetBox()

        LoadDetails()
    End Sub

    Private Sub SaveChanges201314()
        Dim TheData As DataTable

        TheData = TryCast(TheRepeater.DataSource, DataTable)

        If TheData Is Nothing Then Exit Sub

        If TheData Is Nothing Then Exit Sub

        Dim r As DataRow
        Dim v As Decimal
        Dim Status As String
        Dim Skip As Boolean = False


        For Each r In TheData.Rows
            Dim Period_ID As Integer
            Dim DV As DataView

            Skip = False


            If r("RevisedValue") = "" Then
                If r("CurrentValue") = "" Then

                    Skip = True

                Else
                    v = r("CurrentValue").ToString.Replace("£", "").Replace(",", "").Replace("%", "")
                End If
            Else

                v = r("RevisedValue").ToString.Replace("£", "").Replace(",", "").Replace("%", "")
            End If


            If Not Skip Then
                If r("Status") = 1 Then
                    Status = "Approved"
                Else
                    Status = "Draft"
                End If

                DV = LoadDataView("select dbo.GetPeriodIDByNum(" & PeriodBox.zSelectedPeriod_ID & "," & r("Period") & ") as Period_ID")

                If IsDBNull(DV(0)("Period_ID")) Then
                    Exit Sub
                Else
                    Period_ID = DV(0)("Period_ID")
                End If

                Dim sql As String

                sql = "exec usp_Acad_InsertData @Period_ID = " & Period_ID & ", @Dataset_Code = " & DatasetBox.SelectedValue & ", @Field_Code = " & r("FieldCode") & ", @RID = '" & ProviderBox.zSelectedProvider.RID & "', @Value =" & v.ToString & ", @Status = '" & Status & "', @User_Name = '" & CurrentUser.Name & "'"

                RunNonQuery(sql)
            End If
           
        Next

        MessageBox.Show("The changes have been saved", "Changes saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
        LoadDetails()
    End Sub

    Private Sub SaveRevisions()
        If ValidateBoxes() = True Then
            'save

            Dim TheData As DataTable

            TheData = TryCast(TheRepeater.DataSource, DataTable)

            If TheData Is Nothing Then Exit Sub

            Dim r As DataRow
            Dim v As Decimal
            Dim ShouldReplace As Boolean
            Dim ChangesSaved As Boolean

            ChangesSaved = False

            For Each r In TheData.Rows
                ShouldReplace = False

                Select Case r("Type")
                    Case "DATE"

                    Case "YN"
                        If r("YNValue") <> "" Then

                            v = IIf(r("YNValue") = "Yes", 1, 0)
                            ShouldReplace = True
                        End If
                    Case "PERCENT"
                        If r("RevisedValue") <> "" Then
                            v = (r("RevisedValue").ToString.Replace("%", "")) / 100
                            ShouldReplace = True
                        End If

                    Case "MONEY"
                        If r("RevisedValue") <> "" Then

                            Dim s As String

                            s = r("RevisedValue").ToString.Replace("£", "")
                            s = s.Replace(",", "")

                            v = s
                            ShouldReplace = True

                        End If

                    Case Else
                        If r("RevisedValue") <> "" Then
                            v = r("RevisedValue")
                            ShouldReplace = True
                        End If

                End Select


                If ShouldReplace Then

                    'save and replace

                    Dim Period_ID As Integer
                    Dim DV As DataView

                    DV = LoadDataView("select dbo.GetPeriodIDByNum(" & PeriodBox.zSelectedPeriod_ID & "," & r("Period") & ") as Period_ID")

                    If IsDBNull(DV(0)("Period_ID")) Then
                        Exit Sub
                    Else
                        Period_ID = DV(0)("Period_ID")
                    End If

                    Dim sql As String

                    sql = "update Acad_GAG_Data " & _
                            "set Status = 'History' " & _
                            "where Dataset_Code = " & DatasetBox.SelectedValue & " " & _
                            "and Period_ID = " & Period_ID & " " & _
                            "and RID = '" & ProviderBox.zSelectedProvider.RID & "' " & _
                            "and Field_Code = " & r("FieldCode") & " " & _
                            "and Status = 'Approved' "

                    RunNonQuery(sql)

                    sql = "insert into acad_gag_data " & _
                            "(Dataset_Code, Period_ID, RID, Field_Code, TheDate, User_Name, Status, Value) " & _
                            "values (@Dataset_Code, @Period_ID, @RID, @Field_Code, current_timestamp, @User_Name, @Status, @Value) "


                    Dim p As New List(Of SqlClient.SqlParameter)

                    p.Clear()
                    p.Add(New SqlClient.SqlParameter("@Dataset_Code", DatasetBox.SelectedValue))
                    p.Add(New SqlClient.SqlParameter("@Period_ID", Period_ID))
                    p.Add(New SqlClient.SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))
                    p.Add(New SqlClient.SqlParameter("@Field_Code", r("FieldCode")))
                    ' p.Add(New SqlClient.SqlParameter("@TheDate", Now().ToShortDateString))
                    p.Add(New SqlClient.SqlParameter("@User_Name", CurrentUser.Name))
                    p.Add(New SqlClient.SqlParameter("@Status", "Approved"))
                    p.Add(New SqlClient.SqlParameter("@Value", v))

                    RunNonQueryParams(sql, p)
                    ChangesSaved = True

                End If



            Next

            If ChangesSaved Then
                MessageBox.Show("The changes have been saved", "Changes saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
                LoadDetails()

            End If
        Else
            MessageBox.Show("Some of the values entered are incorrect, please check and ammend accordingly.", "Please check.", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If


    End Sub

    Private Function ValidateBoxes() As Boolean
        Dim R As DataRow
        Dim TheTable As DataTable

        TheTable = TryCast(TheRepeater.DataSource, DataTable)

        If TheTable Is Nothing Then

            Return False
            Exit Function

        End If

        For Each R In TheTable.Rows

            If R("RevisedValue") <> "" Then
                Select Case R("Type")

                    Case "DATE"
                        'No need, due to date time picker

                    Case "INT"
                        Try
                            Dim i As Integer
                            i = R("RevisedValue")
                        Catch ex As Exception
                            Return False
                        End Try

                    Case "MONEY"
                        Dim i As Decimal
                        Dim s As String

                        s = R("RevisedValue").ToString.Replace("£", "")
                        s = s.Replace(",", "")
                        Try
                            i = s

                        Catch ex As Exception

                            Return False

                        End Try


                    Case "PERCENT"
                        Dim i As Decimal
                        Dim s As String

                        s = R("RevisedValue").ToString.Replace("%", "")
                        s = s.Replace(",", "")
                        Try
                            i = s

                        Catch ex As Exception

                            Return False

                        End Try

                    Case "YN"
                        'no need, due to combobox

                    Case Else

                End Select
            End If

        Next

        Return True
    End Function

    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click

        If PeriodBox.zSelectedPeriod_ID >= 2013141 Then
            SaveChanges201314()
        Else
            SaveRevisions()
        End If


    End Sub

    Private Sub ProviderBox_ProviderDeselected() Handles ProviderBox.ProviderDeselected
        DetailsPanel.Visible = False
    End Sub

    Private Sub TheRepeater_DrawItem(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles TheRepeater.DrawItem

        Dim TheData As DataTable

        TheData = TryCast(TheRepeater.DataSource, DataTable)

        If TheData Is Nothing Then Exit Sub

        Dim FlowPanel As FlowLayoutPanel

        FlowPanel = TryCast(e.DataRepeaterItem.Controls("ItemPanel"), FlowLayoutPanel)

        If Not FlowPanel Is Nothing Then

            Dim RevBox As TextBox
            Dim YNBox As ComboBox
            Dim DBox As MaskedTextBox
            Dim CurrentBox As Label
            Dim Notes As Label
            Dim StatusBox As ComboBox

            RevBox = TryCast(FlowPanel.Controls("RevisedValueBox"), TextBox)

            YNBox = TryCast(FlowPanel.Controls("YesNoBox"), ComboBox)

            DBox = TryCast(FlowPanel.Controls("DateBox"), MaskedTextBox)

            CurrentBox = TryCast(FlowPanel.Controls("CurrentValueLable"), Label)

            Notes = TryCast(FlowPanel.Controls("NotesLabel"), Label)

            StatusBox = TryCast(FlowPanel.Controls("comboBoxStatus"), ComboBox)


            RevBox.Visible = False
            YNBox.Visible = False
            DBox.Visible = False

            If PeriodBox.zSelectedPeriod_ID >= 2013141 Then
                StatusBox.Visible = True
                Label6.Visible = True

            Else
                StatusBox.Visible = False
                Label6.Visible = False
            End If

            If TheData(e.DataRepeaterItem.ItemIndex)("Formula") = "" Then
                Select Case TheData(e.DataRepeaterItem.ItemIndex)("Type")
                    Case "DATE"
                        DBox.Visible = True
                    Case "YN"
                        YNBox.Visible = True
                    Case "MONEY"
                        RevBox.Visible = True
                        RemoveHandler RevBox.Leave, AddressOf MakeMoney
                        RemoveHandler RevBox.Enter, AddressOf MakeNumber


                        AddHandler RevBox.Leave, AddressOf MakeMoney
                        AddHandler RevBox.Enter, AddressOf MakeNumber
                    Case "PERCENT"
                        RevBox.Visible = True
                        RemoveHandler RevBox.Leave, AddressOf MakePercent
                        RemoveHandler RevBox.Enter, AddressOf MakeNumber

                        AddHandler RevBox.Leave, AddressOf MakePercent
                        AddHandler RevBox.Enter, AddressOf MakeNumber
                    Case Else
                        RevBox.Visible = True
                        RemoveHandler RevBox.Leave, AddressOf FormatToNumber
                        RemoveHandler RevBox.Enter, AddressOf MakeNumber

                        AddHandler RevBox.Leave, AddressOf FormatToNumber
                        AddHandler RevBox.Enter, AddressOf MakeNumber
                End Select
                '     CurrentBox.Font = New System.Drawing.Font(CurrentBox.Font, FontStyle.Regular)

                Dim i As Integer

                i = e.DataRepeaterItem.ItemIndex

                Notes.Text = NotesCount(GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, TheData(i)("period")), DatasetBox.SelectedValue, TheData(i)("FieldCode"), ProviderBox.zSelectedProvider.RID)

                If Notes.Text <> 0 Then
                    Notes.Visible = True
                Else
                    Notes.Visible = False
                End If
            Else

                '     CurrentBox.Font = New System.Drawing.Font(CurrentBox.Font, FontStyle.Bold)
                Notes.Visible = False

            End If



        End If


    End Sub

    Private Function NotesCount(ByVal Period_ID As Integer, ByVal Dataset_Code As Integer, ByVal Field_Code As Integer, ByVal RID As String) As Integer

        Dim sql As String
        Dim dv As DataView

        sql = "select COUNT(*) as counter from Acad_GAG_Notes " & _
                "where Dataset_Code = " & Dataset_Code.ToString & " " & _
                "and Period_ID = " & Period_ID.ToString & " " & _
                "and RID = '" & RID & "' " & _
                "and Field_Code = " & Field_Code.ToString

        dv = LoadDataView(sql)

        If dv.Count <> 0 Then
            Return dv(0)("counter")
        End If


        Return 0
    End Function

    Private Sub MakeMoney(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Try
            DirectCast(sender, TextBox).Text = Decimal.Parse(DirectCast(sender, TextBox).Text).ToString("c")
        Catch ex As Exception

        End Try


    End Sub

    Private Sub MakePercent(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Try
            DirectCast(sender, TextBox).Text = DirectCast(sender, TextBox).Text * 100
            DirectCast(sender, TextBox).Text = DirectCast(sender, TextBox).Text & "%"
        Catch ex As Exception

        End Try


    End Sub

    Private Sub FormatToNumber(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Try
            DirectCast(sender, TextBox).Text = Decimal.Parse(DirectCast(sender, TextBox).Text).ToString("n")

        Catch ex As Exception

        End Try


    End Sub

    Private Sub MakeNumber(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Try
            DirectCast(sender, TextBox).Text = DirectCast(sender, TextBox).Text.Replace("£", "").Replace(",", "")

            If DirectCast(sender, TextBox).Text.Contains("%") Then
                DirectCast(sender, TextBox).Text = DirectCast(sender, TextBox).Text.Replace("%", "") / 100
            End If
        Catch ex As Exception

        End Try


    End Sub




    Private Sub ImportData()
        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to import datasets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Import Datasets", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If


        'reset
        With Acad_ImportWizardDialog

            .ButtonPanel.Enabled = True
            .NextBut.Enabled = True
            .Backbut.Enabled = False
            .TheTabs.SelectedIndex = 0
            .FileBox.Text = ""
            .IgnoreRadio.Checked = True
            .AnalyzeBar.Value = 0
            .ProgressLabel.Text = ""
            .StartBut.Enabled = True

        End With

        If Acad_ImportWizardDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            LoadDetails()

        End If

    End Sub

    Private Sub ImportDataToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportDataToolStripMenuItem.Click
        ImportData()

    End Sub

    Private Sub DatasetButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DatasetButton.Click
        DatasetMenu.Show(DatasetButton, New System.Drawing.Point(0, DatasetButton.Height))
    End Sub

    Private Sub ShowGrid()

        Dim TheData As DataView
        Dim Params As New List(Of Object)

        NotesDV = Nothing
        GridFields = Nothing

        Params.Clear()

        Params.Add(PeriodBox.zSelectedPeriod_ID)
        Params.Add(DatasetBox.SelectedValue)

        If DoSomeWork("LoadAcadDatasetGrid", Params) <> Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If

        TheData = TryCast(BusyButFine.ReturnedValues(0), DataView)

        If TheData Is Nothing Then
            Exit Sub
        End If

        TheGrid.DataSource = TheData

        DetailsPanel.Visible = False

        GridPanel.Dock = DockStyle.Fill
        GridPanel.Visible = True

        'format the grid

        Dim c As DataGridViewColumn

        For Each c In TheGrid.Columns

            If Not c.CellType.Equals(GetType(String)) Then
                c.DefaultCellStyle.Format = "n"
                c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            End If


            Select Case c.Name
                Case "RID"
                    c.Visible = False
                Case "Provider_Name"
                    c.HeaderText = "Provider Name"
                    c.SortMode = DataGridViewColumnSortMode.Automatic
                Case "UPIN"
                    c.DefaultCellStyle.Format = "g"
                    c.SortMode = DataGridViewColumnSortMode.Automatic
                Case "UAI"
                    c.SortMode = DataGridViewColumnSortMode.Automatic
                Case "LAEstab"
                    c.SortMode = DataGridViewColumnSortMode.Automatic
                Case Else
                    'do nothing
                    c.SortMode = DataGridViewColumnSortMode.NotSortable
            End Select


        Next

        LoadGridFields()
        LoadNotesDV()


    End Sub

    Private Sub ViewDatasetAsGridToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ViewDatasetAsGridToolStripMenuItem.Click
        ShowGrid()

    End Sub

    Private Sub ExportToExcel()
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To TheGrid.RowCount - 2
            For j = 0 To TheGrid.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    TheGrid(j, i).Value.ToString()
            Next
        Next

        xlApp.Visible = True


        'xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        'xlWorkBook.Close()
        'xlApp.Quit()

        'releaseObject(xlApp)
        'releaseObject(xlWorkBook)
        'releaseObject(xlWorkSheet)

        'MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

    'Private Sub releaseObject(ByVal obj As Object)
    '    Try
    '        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    '        obj = Nothing
    '    Catch ex As Exception
    '        obj = Nothing
    '    Finally
    '        GC.Collect()
    '    End Try
    'End Sub



    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        'ExportToExcel()

        Dim p As New List(Of Object)

        p.Clear()

        p.Add(TheGrid.DataSource)

        DoSomeWork("ExportToExcel", p)

    End Sub

    Private Sub HistoryButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HistoryButton.Click
        If TheRepeater.CurrentItemIndex < 0 Then Exit Sub

        LoadHistory()


    End Sub

    Private Sub LoadNotes()
        Dim TheData As DataTable

        TheData = TryCast(TheRepeater.DataSource, DataTable)

        If TheData Is Nothing Then Exit Sub

        Dim i As Integer = TheRepeater.CurrentItemIndex

        With Acad_Notes_Form

            .Period_ID = GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, TheData(i)("Period"))
            .Dataset_Code = DatasetBox.SelectedValue
            .Field_Code = TheData(i)("FieldCode")
            .RID = ProviderBox.zSelectedProvider.RID

            .Text = TheData(i)("Description")
            .LoadNotes()
            .Show()

        End With
    End Sub

    Private Sub LoadHistory()

        Dim TheData As DataTable

        TheData = TryCast(TheRepeater.DataSource, DataTable)

        If TheData Is Nothing Then Exit Sub

        Dim i As Integer = TheRepeater.CurrentItemIndex

        Dim HistoryDV As DataView
        Dim SQL As String

        SQL = "select TheDate as [Date], rtrim(User_Name) as [User], Status, Value from Acad_GAG_Data " & _
            "where Dataset_Code = " & DatasetBox.SelectedValue.ToString & " and Field_Code = " & TheData(i)("FieldCode") & " and Period_ID = " & GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, TheData(i)("Period")) & " and RID = '" & ProviderBox.zSelectedProvider.RID & "' " & _
            "order by Date desc "
        HistoryDV = LoadDataView(SQL)

        With AcadHistoryForm
            .Text = TheData(i)("Description") & " history"

            .TheGrid.DataSource = HistoryDV
            .Show()

        End With

    End Sub

    Private Sub LoadGridNotes()
        If TheGrid.SelectedCells.Count = 0 Then Exit Sub

        If TheGrid.CurrentCell Is Nothing Then Exit Sub

        GridFields.RowFilter = "Field_Name = '" & TheGrid.Columns(TheGrid.CurrentCell.ColumnIndex).Name.Replace("'", "''") & "'"

        If GridFields.Count = 0 Then Exit Sub

        Dim RID As String

        RID = TheGrid.Item("RID", TheGrid.CurrentCell.RowIndex).Value


        With Acad_Notes_Form

            .Period_ID = GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, GridFields(0)("Period"))
            .Dataset_Code = DatasetBox.SelectedValue
            .Field_Code = GridFields(0)("Field_Code")
            .RID = RID

            .Text = GridFields(0)("Field_Name")
            .LoadNotes()
            .Show()

        End With
    End Sub


    Private Sub LoadGridHistory()

        If TheGrid.SelectedCells.Count = 0 Then Exit Sub

        If TheGrid.CurrentCell Is Nothing Then Exit Sub

        GridFields.RowFilter = "Field_Name = '" & TheGrid.Columns(TheGrid.CurrentCell.ColumnIndex).Name.Replace("'", "''") & "'"

        If GridFields.Count = 0 Then Exit Sub

        Dim RID As String

        RID = TheGrid.Item("RID", TheGrid.CurrentCell.RowIndex).Value

        Dim HistoryDV As DataView
        Dim SQL As String

        SQL = "select TheDate as [Date], rtrim(User_Name) as [User], Status, Value from Acad_GAG_Data " & _
            "where Dataset_Code = " & DatasetBox.SelectedValue.ToString & " and Field_Code = " & GridFields(0)("Field_Code") & " and Period_ID = " & GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, GridFields(0)("Period")) & " and RID = '" & RID & "' " & _
            "order by Date desc "
        HistoryDV = LoadDataView(SQL)

        With AcadHistoryForm
            .Text = GridFields(0)("Field_Name") & " history"

            .TheGrid.DataSource = HistoryDV
            .Show()

        End With

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        LoadNotes()

    End Sub

    Private Sub TheGrid_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles TheGrid.CellDoubleClick
        Dim RID As String

        RID = TheGrid.CurrentRow.Cells("RID").Value

        ProviderBox.SetProviderByRID(RID)

        'LoadDetails()

    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click

        With FindDialog
            .TheGrid = TheGrid
            .Show()
        End With
    End Sub

    Private Sub TheRepeater_CurrentItemIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TheRepeater.CurrentItemIndexChanged
        'load history if the tool is open
        If AcadHistoryForm.Visible Then
            LoadHistory()

        End If

        If Acad_Notes_Form.Visible Then
            LoadNotes()

        End If

    End Sub

    Private Sub LoadGridFields()
        If TheGrid.RowCount = 0 Then Exit Sub

        If GridPanel.Visible = False Then Exit Sub

        Dim sql As String

        sql = "select Period, Field_Code, Field_Name from acad_gag_templates where dataset_code = " & DatasetBox.SelectedValue

        GridFields = LoadDataView(sql)

        If GridFields.Count <> 0 Then
            Dim r As DataRowView
            Dim PeriodString As String

            For Each r In GridFields

                If r("Field_Name").ToString.Contains("[YEAR]") Then
                    PeriodString = Period_Name(GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, r("Period")))

                    r("Field_Name") = r("Field_Name").ToString.Replace("[YEAR]", PeriodString)


                End If

                If r("Field_Name").ToString.Contains("[YEAR-1]") Then

                    PeriodString = Period_Name(GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, r("Period") - 1))
                    r("Field_Name") = r("Field_Name").ToString.Replace("[YEAR-1]", PeriodString)


                End If

                If r("Field_Name").ToString.Contains("[YEAR+1]") Then

                    PeriodString = Period_Name(GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, r("Period") + 1))
                    r("Field_Name") = r("Field_Name").ToString.Replace("[YEAR+1]", PeriodString)


                End If

            Next


        End If
    End Sub

    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
        LoadGridHistory()

    End Sub

    Private Sub TheGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles TheGrid.CellEnter
        If AcadHistoryForm.Visible = True Then
            LoadGridHistory()

        End If

        If Acad_Notes_Form.Visible = True Then
            LoadGridNotes()

        End If
    End Sub

    Private Sub ToolStripButton4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton4.Click
        LoadGridNotes()

    End Sub

    Private Sub LoadNotesDV()
        Dim sql As String

        sql = "select distinct RID, n.Field_Code, Period_ID from Acad_GAG_Notes n " & _
                      "where n.Dataset_Code = " & DatasetBox.SelectedValue

        NotesDV = LoadDataView(sql)

       
    End Sub

    Private Sub TheGrid_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles TheGrid.CellPainting
        If e.RowIndex = -1 Then
            e.Handled = False
            Exit Sub
        End If

        If e.ColumnIndex = -1 Then
            e.Handled = False
            Exit Sub
        End If

        If NotesDV Is Nothing Then
            e.Handled = False
            Exit Sub
        End If

        NotesDV.RowFilter = ""

        If NotesDV.Count = 0 Then
            e.Handled = False
            Exit Sub
        End If

        Dim FieldName As String

        FieldName = TheGrid.Columns(e.ColumnIndex).Name.Replace("'", "''")

        GridFields.RowFilter = "Field_Name = '" & FieldName & "'"

        If GridFields.Count = 0 Then
            e.Handled = False
            Exit Sub
        End If

        Dim RID As String
        RID = TheGrid.Item("RID", e.RowIndex).Value

        Dim PeriodNum As Integer
        PeriodNum = GridFields(0)("Period")

        Dim notesfilter As String

        notesfilter = "Field_Code = '" & GridFields(0)("Field_Code") & "' and RID = '" & RID & "' and Period_ID = '" & GetPeriodByNum(PeriodBox.zSelectedPeriod_ID, PeriodNum) & "'"

        NotesDV.RowFilter = notesfilter
        If NotesDV.Count = 0 Then
            e.Handled = False
            Exit Sub
        End If

        Dim newRect As New Rectangle(e.CellBounds.X, e.CellBounds.Y, 8, 8)


        Try
            e.Paint(e.ClipBounds, (DataGridViewPaintParts.All))
            e.Graphics.FillRectangle(Brushes.Yellow, newRect)
            e.Graphics.DrawRectangle(Pens.Black, newRect)

            e.Handled = True
        Finally


        End Try


    End Sub

   
    Private Sub DatasetMenu_Opening(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles DatasetMenu.Opening
        If PeriodBox.zSelectedPeriod_ID = 2013141 Then
            ImportDataToolStripMenuItem.Visible = False

        Else
            ImportDataToolStripMenuItem.Visible = True

        End If
    End Sub

    Function GetStatus(DatasetCode As Integer, FieldCode As Integer, PeriodID As Integer, RID As String) As Integer

        Dim sql As String = ""

        sql = "select Status from dbo.Acad_GAG_Data_Latest_Value where Dataset_Code = " & DatasetCode & " and Field_Code = " & FieldCode & " and Period_ID = " & PeriodID & " and RID = '" & RID & "'"

        Dim dv As DataView

        dv = LoadDataView(sql)

        If dv.Count <> 0 Then

            If Trim(dv(0)("Status")) = "Approved" Then
                Return 1
            Else
                Return 0
            End If
        Else
            Return 0

        End If

    End Function

   
    
End Class